package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Trained;
import ModelLayer.TrainingActivity;
import ModelLayer.TrainingDay;
import ModelLayer.Trainer;

public class DBTrained implements DBTrainedIF
{
	private Connection con;
    
    public DBTrained()
    {
    	con = DBConnection.getInstance().getDBcon();
    }
	
    public ArrayList<Trained> getAllTrained(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    
    public ArrayList<Trained> getAllTrainedByTrainingDayID(int trainingdayid, boolean retriveAssociation)
    {
    	String wClause = "t.TrainingDayID = " + trainingdayid + "";
        return miscWhere(wClause, retriveAssociation);
    }

    public Trained searchTrainedID(int trainedID, boolean retriveAssociation)
    {   
    	String wClause = "TrainedID = " + trainedID + "";
        return singleWhere(wClause, retriveAssociation);
    }
    
    private ArrayList<Trained> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Trained> trainedList = new ArrayList<Trained>();	
		
		String query = buildQuery(wClause);
  
  		try
  		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(200);
	 		results = stmt.executeQuery(query);
	 		
			while( results.next() )
			{
				Trained tObj = new Trained();
				tObj = buildTrained(results);	
				trainedList.add(tObj);	
			}
			stmt.close();
		}
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return trainedList;
	}
	
	private Trained singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Trained tObj = new Trained();
	    String query = buildQuery(wClause);
//        System.out.println(query);
		try
		{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() )
	 		{
	 			tObj = buildTrained(results);
	 			stmt.close();
			}
		}	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception: "+e);
	 	}
		return tObj;
	}

	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM Trained t, TrainingActivity tA, TrainingDay tD, Trainer tR, Grownup g, Person p";
	    query += " WHERE t.TrainingActivityID = tA.TrainingActivityID";
	    query += " AND t.TrainingDayID = tD.TrainingDayID";
	    query += " AND tD.TrainerID = tR.TrainerID";
	    query += " AND tR.GrownupID = g.GrownupID";
	    query += " AND g.PersonID = p.PersonID";
	    if (wClause.length()>0)
	    	query += " AND " + wClause;
		return query;
	}
	
	private Trained buildTrained(ResultSet results)
      {   
		Trained tObj = new Trained();
          try
          {
                tObj.setTrainedID(results.getInt(1));
                tObj.setName(results.getString(2));
                tObj.setTrainingActivity(new TrainingActivity(
                								results.getInt(5),
                								results.getString(6)
                						));
                tObj.setTrainingDay(new TrainingDay(
                							results.getInt(8),
                							new Trainer(
                									results.getInt(22),
                									results.getString(23),
                									results.getString(24),
                									results.getString(25),
                									results.getInt(26),
                									results.getString(27),
                									results.getString(28),
                									results.getString(29),
                									results.getString(30),
                									results.getInt(16),
                									results.getString(17),
                									results.getString(18),
                									results.getInt(19)
                							)
                					));
          }
         catch(Exception e)
         {
             System.out.println("error in building the Trained object");
         }
         return tObj;
      }
}
